"""project"""
from datetime import datetime
import math
from flask import g
from psycopg2 import extras
import pytz



def index(page_number, page_size, keyword=None, project_id=None, start_date=None, end_date=None, project_status=None):
    """项目列表"""
    with  g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        base_query = """
            SELECT 
                pr.id, pr.project_name, pr.project_code, pr.start_time, pr.created_at, pr.room_id,
                SUBSTRING(pr.qrcode_base64 FROM '^(data:[^;]+;base64,)') qrcode_base64_type,
                SUBSTRING(pr.qrcode_base64 FROM 'base64,(.*)$') qrcode_base64_content,
                TO_CHAR(TO_TIMESTAMP(pr.start_time) AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') start_time_format, 
                TO_CHAR(TO_TIMESTAMP(pr.created_at) AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') created_at_format
            FROM projects pr
        """

        if project_status == 1:
            base_query += " WHERE pr.deleted_at IS NULL AND EXISTS( SELECT 1 FROM project_person_relation ppr WHERE ppr.deleted_at IS NULL AND ppr.project_id = pr.id AND ppr.status NOT IN (3, 4))"
        else:
            base_query += " WHERE pr.deleted_at IS NULL"

        execute_params = []

        where_clause = ""
        if project_id:
            where_clause += " AND pr.id = %s"
            execute_params.extend([project_id])

        if project_id is None and keyword is not None and keyword != "":
            # 使用 % 进行模糊搜索
            keyword_pattern = f"%{keyword}%"
            where_clause += " AND (pr.project_name LIKE %s OR pr.project_code LIKE %s)"
            execute_params.extend([keyword_pattern, keyword_pattern])

        if start_date and end_date:
            where_clause += " AND pr.created_at BETWEEN %s AND %s"
            execute_params.extend([start_date, end_date])

        query = f"{base_query}{where_clause} ORDER BY pr.created_at DESC LIMIT %s OFFSET %s"
        execute_params.extend([page_size, (page_number - 1) * page_size])

        cursor.execute(query, execute_params)
        return cursor.fetchall()


def insert(data):
    """添加项目"""
    start_time_dt = datetime.strptime(data['start_time'], "%Y-%m-%d %H:%M:%S")
    tz = pytz.timezone('Asia/Shanghai')
    start_time_dt_tz = tz.localize(start_time_dt)
    with g.db_conn.cursor() as cursor:
        query = """
            INSERT INTO 
                projects(project_name, project_code, room_id, start_time, qrcode_base64, created_at, updated_at)
            VALUES(%s, %s, %s, %s, %s, %s, %s) 
            RETURNING id
        """
        params = [
            data["project_name"].strip(),
            data['project_code'].strip(),
            data['room_id'],
            int(start_time_dt_tz.timestamp()),
            data['qrcode_base64'],
            int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp()),
            int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp())
        ]
        cursor.execute(query, params)
        g.db_conn.commit()
        return cursor.fetchone()[0]


def prepare_data_and_params(data):
    """准备数据和参数"""
    set_data = [
        f"{key} = %s" for key, value in data.items() if value and key in ["project_name", "project_code", "room_id"]
    ]
    params = [value for key, value in data.items() if value and key in ["project_name", "project_code", "room_id"]]

    if "start_time" in data and data["start_time"] is not None:
        try:
            start_time_dt = datetime.strptime(data['start_time'], "%Y-%m-%d %H:%M:%S")
            tz = pytz.timezone('Asia/Shanghai')
            start_time_dt_tz = tz.localize(start_time_dt)
            timestamp = int(start_time_dt_tz.timestamp())
        except ValueError:
            return None, None, '请输入正确的时间格式'

        set_data.append("start_time = %s")
        params.append(timestamp)

    if "qrcode_base64" in data and data["qrcode_base64"] is not None:
        set_data.append("qrcode_base64 = %s")
        params.append(data["qrcode_base64"])
    set_data.append("updated_at = %s")
    params.append(int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp()))
    return set_data, params, None


def check_project_exists(cursor, project_id):
    """检查项目是否存在"""
    query = "SELECT EXISTS (SELECT 1 FROM projects WHERE id = %s)"
    cursor.execute(query, [project_id])
    return cursor.fetchone()[0]


def update(cursor, set_data, params, project_id):
    """更新项目"""
    set_clause = ", ".join(set_data)
    query = f"UPDATE projects SET {set_clause} WHERE id = %s"
    params.append(project_id)
    cursor.execute(query, params)
    g.db_conn.commit()


def delete(cursor, project_id):
    """删除项目"""
    query = "UPDATE projects SET deleted_at = %s WHERE id = %s"
    params = [int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp()), project_id]
    cursor.execute(query, params)
    g.db_conn.commit()


def total(keyword=None, project_id=None, start_date=None, end_date=None, project_status=None):
    """项目总数"""
    cursor = g.db_conn.cursor()

    base_query = """SELECT COUNT(*) FROM projects pr"""
    execute_params = []
    where_clause = ""

    if project_status == 1:
        base_query += " WHERE pr.deleted_at IS NULL AND EXISTS( SELECT 1 FROM project_person_relation ppr WHERE ppr.deleted_at IS NULL AND ppr.project_id = pr.id AND ppr.status != 3)"
    else:
        base_query += " WHERE pr.deleted_at IS NULL"

    if project_id:
        where_clause += " AND id = %s"
        execute_params.extend([project_id])

    if project_id is None and keyword is not None and keyword != "":
        # 使用 % 进行模糊搜索
        keyword_pattern = f"%{keyword}%"
        where_clause += " AND (pr.project_name LIKE %s OR pr.project_code LIKE %s)"
        execute_params.extend([keyword_pattern, keyword_pattern])

    if start_date and end_date:
        where_clause += " AND pr.created_at BETWEEN %s AND %s"
        execute_params.extend([start_date, end_date])

    query = f"{base_query}{where_clause}"
    cursor.execute(query, execute_params)
    total_rows = cursor.fetchone()[0]
    cursor.close()
    return total_rows


def page_info(page_number, page_size, total_rows):
    """分页信息"""
    total_pages = math.ceil(total_rows / page_size)
    first_page = 1
    last_page = total_pages if total_pages > 0 else 1

    pre_page_uri = f"/api/project?page_number={max(page_number - 1, first_page)}&page_size={page_size}" if page_number > first_page else None
    next_page_uri = f"/api/project?page_number={min(page_number + 1, last_page)}&page_size={page_size}" if page_number < last_page else None

    return {
        'page_number': page_number,
        'page_size': page_size,
        'total': total_rows,
        'first_page': first_page,
        'last_page': last_page,
        'pre_page_uri': pre_page_uri,
        'next_page_uri': next_page_uri,
    }


def project_exists(project_id, created_at):
    """检查项目是否存在"""
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        base_query = "SELECT id FROM projects pr WHERE project_code = %s AND created_at > %s AND deleted_at IS NULL"
        cursor.execute(base_query, [project_id, created_at])
        return cursor.rowcount


def not_checkout(project_id):
    """未签退的人员数量"""
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        query = """
            SELECT 
                COUNT(*) total
            FROM 
                project_person_relation ppr
            WHERE 
                ppr.deleted_at IS NULL
                AND ppr.project_id = %s
                AND ppr.signout_at is null
        """
        cursor.execute(query, [project_id])
        return cursor.fetchone()['total']


def project_status(project_id, status):
    """修改项目状态为已结束"""
    with g.db_conn.cursor() as cursor:
        query = "UPDATE projects SET status = %s WHERE id = %s"
        cursor.execute(query, (status, project_id))
        g.db_conn.commit()